<?php

namespace app\api\service\admin;

use app\common\library\Auth;
use think\Validate;
use think\Db;
use think\Log;

use fast\Random;
use Exception;
use think\exception\PDOException;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class LeadService
{
    // 列表
    public static function index($data)
    {
        $auth = Auth::instance();
        $limit = isset($data['limit']) ? (int)$data['limit'] : 10;
        $surname = isset($data['surname']) ? trim($data['surname']) : null;
        $given_name = isset($data['given_name']) ? trim($data['given_name']) : null;
        $enlish_name = isset($data['enlish_name']) ? trim($data['enlish_name']) : null;
        $studio_id = isset($data['studio_id']) ? trim($data['studio_id']) : null;
        $channel = isset($data['channel']) ? trim($data['channel']) : null;
        $age = isset($data['age']) ? (int)$data['age'] : null;
        $gender = isset($data['gender']) ? trim($data['gender']) : null;
        $phone = isset($data['phone']) ? trim($data['phone']) : null;
        $collected_by = isset($data['collected_by']) ? trim($data['collected_by']) : null;
        $collected_date = isset($data['collected_date']) ? trim($data['collected_date']) : null;
        $start_date = isset($data['start_date']) ? trim($data['start_date']) : null;
        $end_date = isset($data['end_date']) ? trim($data['end_date']) : null;

        $where = ['uc.status' => 'lead'];

        if ($surname) $where['uc.surname'] = ['like', '%'.$surname.'%'];
        if ($given_name) $where['uc.given_name'] = ['like', '%'.$given_name.'%'];
        if ($enlish_name) $where['uc.surname|uc.given_name'] = ['like', '%'.$enlish_name.'%'];
        if ($studio_id) $where['uc.studio_id'] = ['like', '%'.$studio_id.'%'];
        if ($channel) $where['uc.channel'] = $channel;
        if ($age) $where['uc.birthday'] = ['like', '%'.(date('Y')-$age).'%'];
        if ($gender) $where['uc.gender'] = $gender;
        if ($phone) $where['uc.mobile'] = ['like', '%'.$phone.'%'];
        if ($collected_by) $where['u.nickname'] = ['like', '%'.$collected_by.'%'];

        // 处理日期条件
        if ($collected_date) {
            $where['uc.createtime'] = [
                'between',
                [
                    strtotime($collected_date.' 00:00:00'),
                    strtotime($collected_date.' 23:59:59')
                ]
            ];
        } elseif ($start_date || $end_date) {
            if ($start_date) $where['uc.createtime'] = ['>=', strtotime($start_date)];
            if ($end_date) $where['uc.createtime'] = ['<=', strtotime($end_date)];
        }

        // 分页查询
        $studio = \app\admin\model\UserCustomers::alias('uc')
            ->join('__USER__ u', 'uc.collected_id = u.id')
            ->where($where)
            ->field('uc.id,uc.studio_id,uc.surname,uc.given_name,uc.english_name,uc.birthday,uc.gender,uc.mobile as phone,uc.channel,uc.collected_id,uc.createtime')
            ->order('uc.id', 'desc')
            ->paginate($limit)
            ->each(function($item, $key) {
                $item['birthday'] = substr($item['birthday'], 0, 4);
                $item['age'] = (int)date('Y') - $item['birthday'];
                $item['collected_by'] = $item['collected_id'] ? \app\admin\model\User::where('id', $item['collected_id'])->value('nickname') : '';
                $item['collected_date'] = date('Y-m-d H:i:s', $item['createtime']);
                return $item;
            });

        return ['code' => 200, 'msg' => 'Successful', 'state' => 'OK', 'data' => $studio];
    }


    // 添加
    public static function add($data)
    {
        $data['mobile'] = $data['phone'];

        // 使用空值合并运算符简化
        $fields = [
            'primary_relationship', 'primary_name', 'primary_phone', 'primary_email', 'primary_account',
            'secondary_relationship', 'secondary_name', 'secondary_phone', 'secondary_email', 'secondary_account'
        ];

        foreach ($fields as $field) {
            $data[$field] = $data[$field] ?? '';
        }

        unset($data['phone']);
        $auth = Auth::instance();

        // 验证规则
        $validate = new Validate([
            'studio_id' => 'require',
            'surname' => 'require',
            'given_name' => 'require',
            'english_name' => 'require',
            'status'    =>  'require',
            'gender' => 'require',
            'birthday' => 'require',
            'mobile' => 'require',
            'channel' => 'require',
            'school' => 'require',
            'school_grade' => 'require',
            'description' => 'require',
            'avatar' => 'require',
            'primary_relationship' => 'require',
            'primary_name' => 'require',
            'primary_phone' => 'require',
            'primary_account' => 'require',
        ], [
            'studio_id.require' => 'StudioID is not empty',
            'surname.require' => 'Surname is not empty',
            'given_name.require' => 'Given name is not empty',
            'english_name.require' => 'English name is not empty',
            'status.require' => 'Status is not empty',
            'gender.require' => 'Gender is not empty',
            'birthday.require' => 'Birthday is not empty',
            'mobile.require' => 'Mobile is not empty',
            'channel.require' => 'Channel is not empty',
            'school.require' => 'School is not empty',
            'school_grade.require' => 'School Grade is not empty',
            'description.require' => 'Description not empty',
            'avatar.require' => 'Avatar is not empty',
            'primary_relationship.require' => 'Primary Relationship is not empty',
            'primary_name.require' => 'Primary Name is not empty',
            'primary_phone.require' => 'Primary Phone is required',
            'primary_account.require' => 'Primary Social Acount is not empty',
        ]);

        if (!$validate->check($data)) {
            return ['code'=>400, 'msg'=>$validate->getError(), 'state'=>'INVALID_ARGUMENT', 'data'=>null];
        }

        // 检查学生信息是否已存在（基于学生关键信息）
        $studentExists = \app\admin\model\UserCustomers::where([
            'surname' => $data['surname'],
            'given_name' => $data['given_name'],
            'birthday' => $data['birthday'],
            'studio_id' => $data['studio_id']
        ])->find();

        if ($studentExists) {
            return ['code'=>400, 'msg'=>'Student already exists', 'state'=>'STUDENT_EXISTS', 'data'=>null];
        }

        // 启用事务将数据保存到数据库
        Db::startTrans();
        try {
            // 检查家长手机号是否已注册
            $parentUser = \app\admin\model\User::where('mobile', $data['primary_phone'])
                ->where('studio_id', $data['studio_id'])
                ->find();

            if ($parentUser) {
                // 家长已存在，检查是否为系统管理员等特殊角色
                if (in_array($parentUser['group_id'], [1,2,3,4,5,6])) {
                    return ['code'=>400, 'msg'=>'Mobile is occupied by system user', 'state'=>'INVALID_ARGUMENT', 'data'=>null];
                }

                // 直接关联学生信息到现有家长账号
                $data['user_id'] = $parentUser['id'];
                $data['collected_id'] = $auth->id;
                $student = \app\admin\model\UserCustomers::create($data);

                if (!$student) {
                    throw new Exception('Failed to create student record');
                }
            } else {
                // 家长不存在，需要同时创建家长账号和学生信息

                // 生成随机密码
                $password = bin2hex(random_bytes(4)); // 8字符随机密码

                // 创建家长账号
                $parent = $auth->register(
                    $data['primary_phone'],
                    $password,
                    '',
                    $data['primary_phone'],
                    [
                        'group_id' => 10000, // 普通家长角色
                        'studio_id' => $data['studio_id'],
                        'nickname' => $data['surname'] . $data['given_name'] . "'s " . $data['primary_name'],
                        'plaintext' => $password,
                    ]
                );

                if (!$parent) {
                    throw new Exception('Failed to create parent account');
                }

                // 创建学生信息并关联到新创建的家长账号
                $data['user_id'] = $parent;
                $data['collected_id'] = $auth->id;
                $student = \app\admin\model\UserCustomers::create($data);

                if (!$student) {
                    throw new Exception('Failed to create student record');
                }
            }

            Db::commit();
            return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>$student];
        } catch (\Exception $e) {
            Db::rollback();
//            Log::error('Add student error: ' . $e->getMessage());
            return ['code' => 400, 'msg' => 'Database error', 'state' => 'DATABASE_ERROR', 'data' => null];
        }
    }



    // 编辑
    public static function edit($data)
    {
        if (empty($data) || !is_array($data)) {
            return ['code' => 400, 'msg' => 'Invalid input data', 'state' => 'INVALID_ARGUMENT', 'data' => null];
        }

        // 设置默认值
        $data['mobile'] = $data['phone'] ?? '';
        $fields = [
            'primary_relationship', 'primary_name', 'primary_phone', 'primary_email', 'primary_account',
            'secondary_relationship', 'secondary_name', 'secondary_phone', 'secondary_email', 'secondary_account'
        ];

        foreach ($fields as $field) {
            $data[$field] = $data[$field] ?? '';
        }

        unset($data['phone']);

        // 验证规则
        $validate = new Validate([
            'studio_id' => 'require',
            'surname' => 'require',
            'given_name' => 'require',
            'english_name' => 'require',
            'status'    =>  'require',
            'gender' => 'require',
            'birthday' => 'require',
            'mobile' => 'require',
            'channel' => 'require',
            'school' => 'require',
            'school_grade' => 'require',
            'description' => 'require',
            'avatar' => 'require',
            'primary_relationship' => 'require',
            'primary_name' => 'require',
            'primary_phone' => 'require',
            'primary_account' => 'require',
        ], [
            'studio_id.require' => 'StudioID is not empty',
            'surname.require' => 'Surname is not empty',
            'given_name.require' => 'Given name is not empty',
            'english_name.require' => 'English name is not empty',
            'status.require' => 'Status is not empty',
            'gender.require' => 'Gender is not empty',
            'birthday.require' => 'Birthday is not empty',
            'mobile.require' => 'Mobile is not empty',
            'channel.require' => 'Channel is not empty',
            'school.require' => 'School is not empty',
            'school_grade.require' => 'School Grade is not empty',
            'description.require' => 'Description not empty',
            'avatar.require' => 'Avatar is not empty',
            'primary_relationship.require' => 'Primary Relationship is not empty',
            'primary_name.require' => 'Primary Name is not empty',
            'primary_phone.require' => 'Primary Phone is already exists',
            'primary_account.require' => 'Primary Social Acount is not empty',
        ]);

        if (!$validate->check($data)) {
            return ['code' => 400, 'msg' => $validate->getError(), 'state' => 'INVALID_ARGUMENT', 'data' => null];
        }

        // 启用事务将数据保存到数据库
        Db::startTrans();
        try {
            $result = \app\admin\model\UserCustomers::update($data);

            if ($result) {
                Db::commit();
                return ['code' => 200, 'msg' => 'Successful', 'state' => 'OK', 'data' => $result];
            } else {
                Db::rollback();
                return ['code' => 400, 'msg' => 'Failed', 'state' => 'FAILED', 'data' => null];
            }
        } catch (\Exception $e) {
            Db::rollback();
            return ['code' => 400, 'msg' => 'Database error', 'state' => 'DATABASE_ERROR', 'data' => null];
        }
    }


    // 删除
    public static function del($data)
    {
        $auth = Auth::instance();
        // 验证$data['id']是否为空且为正整数
        if (empty($data['id']) || !ctype_digit((string)$data['id'])) {
            return ['code'=>400, 'msg'=>'Invalid parameters', 'state'=>'INVALID_ARGUMENT', 'data'=>null];
        }

        // 查询学习馆数据
        $details = \app\admin\model\UserCustomers::find($data['id']);
        if (!$details) {
            return ['code'=>400, 'msg'=>'Customer does not exist', 'state'=>'NOT_FOUND', 'data'=>null];
        }

        // 启用事务删除数据
        Db::startTrans();
        try {
            $result = \app\admin\model\UserCustomers::destroy($data['id']);

            if($result){
                Db::commit();
                return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>$result];
            }else{
                Db::rollback();
                return ['code'=>400, 'msg'=>'Failed', 'state'=>'FAILED', 'data'=>null];
            }
        }
        catch (\Exception $e) {
            Db::rollback();
            return ['code' => 400, 'msg' => 'Database operation failed', 'state' => 'DATABASE_ERROR', 'data' => null];
        }
    }


    // 详情
    public static function view($data)
    {
        $auth = Auth::instance();
        if (empty($data['id']) || !is_numeric($data['id'])) {
            return ['code' => 400, 'msg' => 'Invalid parameters', 'state' => 'INVALID_ARGUMENT', 'data' => null];
        }

        try {
            Db::startTrans(); // 添加事务开始

            $result = \app\admin\model\UserCustomers::field('avatar,studio_id,surname,given_name,english_name,status,birthday,gender,mobile,channel,school,school_grade,description,primary_relationship,primary_name,primary_phone,primary_email,primary_account,secondary_relationship,secondary_name,secondary_phone,secondary_email,secondary_account,collected_id,createtime,assign_to,assign_by,assigntime')->find($data['id']);

            if ($result) {
                // 计算年龄 - 更准确的方法
                $birthday = $result['birthday'] ?? null;
                if ($birthday) {
                    $birthDate = new \DateTime($birthday);
                    $currentDate = new \DateTime();
                    $ageInterval = $currentDate->diff($birthDate);
                    $result['age'] = $ageInterval->y;
                } else {
                    $result['age'] = null;
                }

                $result['avatar'] = $result['avatar'] ?: '/assets/img/avatar.png';

                // 合并用户和用户组查询
                $collected = \app\admin\model\User::where('user.id', $result['collected_id'])
                    ->with(['group' => function($query) {
                        $query->field('group.id,name');
                    }])
                    ->field('user.id,surname,given_name,group_id')
                    ->find();

                $result['collected_by'] = $collected ? $collected['given_name'] . $collected['surname'] : null;
                $result['collected_role'] = $collected->group->name ?? null;
                $result['collected_date'] = date('Y-m-d H:i:s', $result['createtime']);

                // 处理分配信息
                $assign = null;
                if ($result['assign_by']) {
                    $assign = \app\admin\model\User::where('user.id', $result['assign_by'])
                        ->with(['group' => function($query) {
                            $query->field('group.id,name');
                        }])
                        ->field('user.id,surname,given_name,group_id')
                        ->find();
                }

                $result['assigned_by'] = $assign ? $assign['given_name'] . $assign['surname'] : '';
                $result['assigned_role'] = $assign->group->name ?? '';
                $result['assigned_date'] = $result['assigntime'] ? date('Y-m-d H:i:s', $result['assigntime']) : '';

                Db::commit();
                return ['code' => 200, 'msg' => 'Successful', 'state' => 'OK', 'data' => $result];
            } else {
                Db::rollback();
                return ['code' => 400, 'msg' => 'Failed', 'state' => 'FAILED', 'data' => null];
            }
        } catch (\Exception $e) {
            Db::rollback();
            return ['code' => 400, 'msg' => 'Database error: ' . $e->getMessage(), 'state' => 'DATABASE_ERROR', 'data' => null];
        }
    }


    // Collect By
    public static function collect_by($data)
    {
        // 验证输入是否为数组
        if (!is_array($data)) {
            return ['code' => 400, 'msg' => 'Invalid input data', 'state' => 'INVALID_ARGUMENT', 'data' => null];
        }

        // 处理字段映射
        $processedData = [
            'group_id' => $data['role'] ?? null,
            'collected_id' => $data['collected_by'] ?? null,
            'createtime' => isset($data['collected_date']) && $data['collected_date'] ? strtotime($data['collected_date']) : time()
        ];

        // 验证时间转换结果
        if (isset($data['collected_date']) && $data['collected_date'] && $processedData['createtime'] === false) {
            return ['code' => 400, 'msg' => 'Invalid date format', 'state' => 'INVALID_ARGUMENT', 'data' => null];
        }

        // 保留原始ID
        if (isset($data['id'])) {
            $processedData['id'] = $data['id'];
        }

        // 验证规则
        $validate = new Validate([
            'id' => 'require|number',
            'group_id' => 'require',
            'collected_id' => 'require',
            'createtime' => 'require',
        ], [
            'id' => 'Invalid parameter',
            'group_id.require' => 'Role is not empty',
            'collected_id.require' => 'Collected By is not empty',
            'createtime.require' => 'Collected Date is not empty',
        ]);

        if (!$validate->check($processedData)) {
            return ['code' => 400, 'msg' => $validate->getError(), 'state' => 'INVALID_ARGUMENT', 'data' => null];
        }

        // 启用事务将数据保存到数据库
        Db::startTrans();
        try {
            $result = \app\admin\model\UserCustomers::update([
                'id' => $processedData['id'],
                'collected_id' => $processedData['collected_id'],
                'createtime' => $processedData['createtime']
            ]);

            if ($result) {
                Db::commit();
                return ['code' => 200, 'msg' => 'Successful', 'state' => 'OK', 'data' => $result];
            } else {
                Db::rollback();
                return ['code' => 400, 'msg' => 'Failed', 'state' => 'FAILED', 'data' => null];
            }
        } catch (\Exception $e) {
            Db::rollback();
            return ['code' => 400, 'msg' => 'Database error: ' . $e->getMessage(), 'state' => 'DATABASE_ERROR', 'data' => null];
        }
    }


    // Assign By
    public static function assign_by($data)
    {
        $data['group_id'] = $data['role'];
        $data['assigntime'] = isset($data['assign_date']) && $data['assign_date'] ? strtotime($data['assign_date']) : time();

        unset($data['role']);

        $auth = Auth::instance();
        // 验证规则
        $validate = new Validate([
            'id' => 'require|number',
            'group_id' => 'require',
            'assign_by' => 'require|number',
            'assign_to' => 'require|number',
            'assigntime' => 'require',
        ], [
            'id' => 'Invalid parameter',
            'group_id.require' => 'Role is not empty',
            'assign_by.require' => 'Assign By is not empty',
            'assign_to.require' => 'Assign To is not empty',
            'assigntime.require' => 'Assign Date is not empty',
        ]);

        if (!$validate->check($data)) {
            return ['code'=>400, 'msg'=>$validate->getError(), 'state'=>'INVALID_ARGUMENT', 'data'=>null];
        }

        // 启用事务将数据保存到数据库
        Db::startTrans();
        try {
            unset($data['group_id']);
            unset($data['assign_date']);
            $result = \app\admin\model\UserCustomers::update($data);

            if($result){
                Db::commit();
                return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>$result];
            }else{
                Db::rollback();
                return ['code'=>400, 'msg'=>'Failed', 'state'=>'FAILED', 'data'=>null];
            }
        }
        catch (\Exception $e) {
            Db::rollback();
            // 生产环境中应记录详细错误日志但返回通用错误信息
            return ['code' => 400, 'msg' => 'Database operation failed', 'state' => 'DATABASE_ERROR', 'data' => null];
        }
    }


    // 导入
    public static function import($data)
    {
        $auth = Auth::instance();

        $file = $data['file'];

        if (!$file) {
            return ['code'=>400, 'msg'=>'Parameter %s can not be empty', 'state'=>'Not Found', 'data'=>null];
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            return ['code'=>400, 'msg'=>'No results were found', 'state'=>'Not Found', 'data'=>null];
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['xls', 'xlsx'])) {
            return ['code'=>400, 'msg'=>'Unknown data format', 'state'=>'Unknown data format', 'data'=>null];
        }
        if ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
        $importHeadType = 'comment';

        $table = 'fa_user_customers';
        $database = \think\Config::get('database.database');
        $fieldArr = [];
        $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);

        foreach ($list as $k => $v) {
            if ($importHeadType == 'comment') {
                $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            } else {
                $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }
        }

        // 非默认值
//        $fieldDate = Db::name('activity_offline_fields')->where(['activity_id'=>$activity_id,'iscontribute'=>1])->column('title,name');
//        $fieldArr = $fieldArr + $fieldDate;

//        return $fieldArr;

        //加载文件

        try {
            if (!$PHPExcel = $reader->load($filePath)) {
                return ['code'=>400, 'msg'=>'Unknown data format', 'state'=>'Unknown data format', 'data'=>null];
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = $val;
                }
            }
//             return $fields;
//            $number = $this->getNumber($activity_id);// 此处编号只计数
            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : $val;
                }

                $row = [];
                $insert = [];
                $temp = array_combine($fields, $values);
//                 return $temp;
//                 return $fieldArr;
                $data = [];
                foreach ($temp as $k => $v) {
                    if (isset($fieldArr[$k])) {
                        $row[$fieldArr[$k]] = $v;
                    }
                    $data[] = $fieldArr[$k];
                }

                if ($row) {


//                    $time = intval(($row['birthday'] - 25569) * 3600 * 24); //转换成1970年以来的秒数
                    $row['birthday'] = date('Y/m/d', strtotime($row['birthday']));

//                    $time = intval(($row['createtime'] - 25569) * 3600 * 24); //转换成1970年以来的秒数
                    $row['createtime'] = time();

//                    $time = intval(($row['updatetime'] - 25569) * 3600 * 24); //转换成1970年以来的秒数
                    $row['updatetime'] = time();



//                    return $row;
                    unset($row['id']);
                    $insert[] = $row;
                }
//                 return $row;
                // return $applydata;
            }
        } catch (Exception $exception) {
            $msg = $exception->getMessage();
            return ['code'=>400, 'msg'=>$msg, 'state'=>'Import failure', 'data'=>null];

        }
//        return $insert;
        if (!$insert) {
            return ['code'=>400, 'msg'=>'No rows were updated', 'state'=>'Import failure', 'data'=>null];

        }

        try {
            //是否包含admin_id字段
            $has_admin_id = false;
            foreach ($fieldArr as $name => $key) {
                if ($key == 'admin_id') {
                    $has_admin_id = true;
                    break;
                }
            }
            if ($has_admin_id) {
                $auth = Auth::instance();
                foreach ($insert as &$val) {
                    if (!isset($val['admin_id']) || empty($val['admin_id'])) {
                        $val['admin_id'] = $auth->isLogin() ? $auth->id : 0;
                    }
                }
            }

            // 使用INSERT IGNORE插入，忽略重复数据
            Db::name('user_customers')->insertAll($insert, ['ignore' => true]);
        } catch (PDOException $exception) {
            $msg = $exception->getMessage();
            if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
                $msg = "导入失败，包含【{$matches[1]}】的记录已存在";
            };
            return ['code'=>400, 'msg'=>$msg, 'state'=>'Import failure', 'data'=>null];
        } catch (Exception $e) {
            $msg = $e->getMessage();
            return ['code'=>400, 'msg'=>$msg, 'state'=>'Import failure', 'data'=>null];
        }
        return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>null];
    }

    // 导出
    public static function export()
    {
        $limit = isset($data['limit']) ? $data['limit'] : 10;
        $surname = isset($data['surname']) ? $data['surname'] : '';
        $given_name = isset($data['given_name']) ? $data['given_name'] : '';
        $enlish_name = isset($data['enlish_name']) ? $data['enlish_name'] : '';
        $studio_id = isset($data['studio_id']) ? $data['studio_id'] : '';
        $channel = isset($data['channel']) ? $data['channel'] : '';
        $age = isset($data['age']) ? $data['age'] : '';
        $gender = isset($data['gender']) ? $data['gender'] : '';
        $phone = isset($data['phone']) ? $data['phone'] : '';
        $collected_by = isset($data['collected_by']) ? $data['collected_by'] : '';
        $collected_date = isset($data['collected_date']) ? $data['collected_date'] : '';
        $start_date = isset($data['start_date']) ? $data['start_date'] : '';
        $end_date = isset($data['end_date']) ? $data['end_date'] : '';
        $where = [];
        if($surname)$where['uc.surname'] = ['like', '%'.$surname.'%'];
        if($given_name)$where['uc.given_name'] = ['like', '%'.$given_name.'%'];
        if($enlish_name)$where['uc.surname|uc.given_name'] = ['like', '%'.$enlish_name.'%'];
        if($studio_id)$where['uc.studio_id'] = ['like', '%'.$studio_id.'%'];
        if($channel)$where['uc.channel'] = $channel;
        if($age)$where['uc.birthday'] = ['like', '%'.(date('Y')-$age).'%'];
        if($gender)$where['uc.gender'] = $gender;
        if($phone)$where['uc.mobile'] = ['like', '%'.$phone.'%'];
        if($collected_by) $where['u.nickname'] = ['like', '%'.$collected_by.'%'];
        if($collected_date) $where['uc.createtime'] = ['between' , [strtotime($collected_date.'00:00:00'), strtotime($collected_date.'23:59:59')]];
        if($start_date) $where['uc.createtime'] = ['>=', strtotime($start_date)];
        if($end_date) $where['uc.createtime'] = ['<=', strtotime($end_date)];



        $formdata = Db::name('user_customers')->where($where)->field('id,collected_id,user_id,description,avatar,createtime,updatetime,assign_to,assign_by,assigntime',true)->select();
        // $formdata = Db::name('activity_offline_player')->where(['activity_id'=>$activity_id])->select();

        foreach($formdata as $k =>$v){
//            $applydata = json_decode($v['applydata'],true);
            foreach($v as $field => $value){
                if(is_array($value)){
                    $formdata[$k][$field] = implode(",", $value);
                }else{
                    $formdata[$k][$field] = $value;
                }
            }
            // 性别
//            if(isset($formdata[$k]['gender'])){
//                if($formdata[$k]['gender'] == 1){
//                    $formdata[$k]['gender'] = '男';
//                }else{
//                    $formdata[$k]['gender'] = '女';
//                }
//            }
//            unset($formdata[$k]['applydata']);
        }

        // 默认字段
      $fieldList = [
          'studio_id'                 => '学习馆',
          'surname'                   => '姓氏',
          'given_name'                => '名字',
          'english_name'              => '英文名',
          'mobile'                    => '手机号',
          'gender'                    => '性别',
          'birthday'                  => '生日',
          'channel'                   => '渠道',
          'school'                    => '学校',
          'school_grade'              => '年级',
          'primary_relationship'    => '紧急联系人关系',
          'primary_name'   => '紧急联系人电话',
          'primary_phone'   => '紧急联系人电话',
          'primary_email' => '紧急联系人邮箱',
          'primary_account'    => '紧急联系人社交帐号',
          'secondary_relationship'    => '次级联系人关系',
          'secondary_name'    => '次级联系人姓名',
          'secondary_phone'   => '次级联系人电话',
          'secondary_email'   => '次级联系人邮箱',
          'secondary_account' => '次级联系人社交帐号',
          'status'                    => '状态',
      ];

        $title_txt = [];
        $val = [];
        foreach($formdata as $k => $v){
            $title = array_keys($v);
            $val[$k] = array_values($v);
        }
//        return $fieldList;
        foreach($title as $k => $v){
            if(isset($fieldList[$v])) $title_txt[] = $fieldList[$v];
        }
//        return $title_txt;
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();

        //设置工作表标题名称
        $worksheet->setTitle('工作表格1');

        //表头
        //设置单元格内容
        foreach ($title_txt as $key => $value) {
            $cloumn = self::getColumn($key);
            $worksheet->setCellValue($cloumn."1",$value);// 充填标题
            $worksheet->getStyle($cloumn."1")->getFont()->setBold(true);// 设置字体
//            if(in_array($value,['身份证'])){
//                $worksheet->getColumnDimension($cloumn)->setWidth(20);// 设置宽度
//            }elseif(in_array($value,['手机','联系人手机'])){
//                $worksheet->getColumnDimension($cloumn)->setWidth(15);// 设置宽度
//            }else{
//                $worksheet->getColumnDimension($cloumn)->setWidth(10);// 设置宽度
//            }
        }

        $row = 2; //从第二行开始
        foreach ($val as $item) {
            $column = 1;

            foreach ($item as $k => $value) {

                $list = self::getColumn($k);
                $worksheet->setCellValue($list.$row,$value);// 充填内容
                $worksheet->getStyle($list.$row)->getNumberFormat() ->setFormatCode(NumberFormat::FORMAT_NUMBER);// 设置格式
                $column++;
            }
            $row++;
        }

        // exit;

        $fileName = 'Customers' . date('YmdHis');
        $fileType = 'Xlsx';

        if($fileType == 'Excel2007' || $fileType == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="'.$fileName.'.xlsx"');
            header('Cache-Control: max-age=0');
        } else { //Excel5
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="'.$fileName.'.xls"');
            header('Cache-Control: max-age=0');
        }

        //2.输出到浏览器
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); //按照指定格式生成Excel文件
        // excelBrowserExport($fileName, 'Xlsx');
        $writer->save('php://output');


    }

    public static function getNumber($activity_id)
    {
        // 此处编号只计数
        $number = Db::name('activity_offline_player')->where('activity_id', $activity_id)->max('number');
        $number = $number ? $number : 0;
        $number = $number + 1;
        return $number;
    }

    public static function getColumn($i)
    {
        $y = ($i / 26);
        if ($y >= 1) {
            $y = intval($y);
            $res = chr($y+64).chr($i-$y*26 + 65);
        } else {
            $res = chr($i+65);

        }
        return $res;
    }

    //支付记录
    public static function payment($data)
    {
        try {
            // 参数验证
            if (!isset($data['customers_id']) || empty($data['customers_id'])) {
                return ['code' => 400, 'msg' => 'Invalid customers_id', 'state' => 'ERROR'];
            }

            $customers_id = intval($data['customers_id']);
            $limit = isset($data['limit']) ? intval($data['limit']) : 10;
            $limit = max(1, min($limit, 100)); // 限制limit范围1-100

            $list = \app\admin\model\CustomersOrder::where('customers_id', $customers_id)
                ->order('id desc')
                ->paginate($limit)
                ->each(function($item, $key) {
                    if ($item['type'] == 'appontment') {
                        $info = \app\admin\model\CustomersAppointment::where('id', $item['link_id'])->find();
                        if ($info) {
                            $item['all_payment'] = $info['type'];
                            $item['number'] = $info['number'];
                            // 如果需要日期，取消下面注释
                            // $item['paid_date'] = date('Y-m-d H:i:s', $item['paidtime']);
                        }
                    }
                });

            return ['code' => 200, 'msg' => 'Successful', 'state' => 'OK', 'data' => $list];

        } catch (\Exception $e) {
            return ['code' => 500, 'msg' => 'Server Error: ' . $e->getMessage(), 'state' => 'ERROR'];
        }
    }


    // 新增合同
    public static function add_contract($data)
    {
        $auth = Auth::instance();

        // 验证规则
        $validate = new Validate([
            'customers_id' => 'require|number',
            'contract_type' => 'require',
            'course_category' => 'require',
            'contract_lessons' => 'require|number|gt:0',
            'contract_length' => 'require',
            'start_date' => 'require|date',
            'end_date' => 'require|date|after:start_date',
            'contract_lessons_paid' => 'require|number',
            'contract_lessons_free' => 'require|number',
            'start_level' => 'require',
            'end_level' => 'require',
            'cool_coin' => 'require|number',
            'consultant' => 'require',
            'in_charge_teacher' => 'require',
            'teacher_kpi' => 'require',
            'currency' => 'require',
            'contract_tuition' => 'require|number|gt:0',
            'schoolarship_amount' => 'require|number',
            'contract_amount' => 'require|number|gt:0',
            'book_fee' => 'require|number|egt:0',
            'enrollment_fee' => 'require|number|egt:0',
            'status' => 'require',
            'payment_times' => 'require|number|between:1,4',
            'payment_time_1' => 'require',
            'payment_amount_1' => 'require|number|gt:0',
            'payment_lessons_1' => 'require|number|gt:0',
            'payment_time_2' => 'requireIf:payment_times,2,3,4',
            'payment_amount_2' => 'requireIf:payment_times,2,3,4|number',
            'payment_lessons_2' => 'requireIf:payment_times,2,3,4|number',
            'payment_time_3' => 'requireIf:payment_times,3,4',
            'payment_amount_3' => 'requireIf:payment_times,3,4|number',
            'payment_lessons_3' => 'requireIf:payment_times,3,4|number',
            'payment_time_4' => 'requireIf:payment_times,4',
            'payment_amount_4' => 'requireIf:payment_times,4|number',
            'payment_lessons_4' => 'requireIf:payment_times,4|number',
        ], [
            'customers_id.require' => 'Customers ID is required',
            'customers_id.number' => 'Customers ID must be a number',
            'contract_type.require' => 'Contract Type is required',
            'course_category.require' => 'Course Category is required',
            'contract_lessons.require' => 'Contract Lessons is required',
            'contract_lessons.number' => 'Contract Lessons must be a number',
            'contract_lessons.gt' => 'Contract Lessons must be greater than 0',
            'contract_length.require' => 'Contract Length is required',
            'start_date.require' => 'Start Date is required',
            'start_date.date' => 'Start Date must be a valid date',
            'end_date.require' => 'End Date is required',
            'end_date.date' => 'End Date must be a valid date',
            'end_date.after' => 'End Date must be after Start Date',
            'contract_lessons_paid.require' => 'Paid Lessons is required',
            'contract_lessons_paid.number' => 'Paid Lessons must be a number',
            'contract_lessons_free.require' => 'Free Lessons is required',
            'contract_lessons_free.number' => 'Free Lessons must be a number',
            'start_level.require' => 'Start Level is required',
            'end_level.require' => 'End Level is required',
            'cool_coin.require' => 'Cool Coin is required',
            'cool_coin.number' => 'Cool Coin must be a number',
            'consultant.require' => 'Consultant is required',
            'in_charge_teacher.require' => 'In Charge Teacher is required',
            'teacher_kpi.require' => 'Teacher KPI is required',
            'currency.require' => 'Currency is required',
            'contract_tuition.require' => 'Contract Tuition is required',
            'contract_tuition.number' => 'Contract Tuition must be a number',
            'contract_tuition.gt' => 'Contract Tuition must be greater than 0',
            'schoolarship_amount.require' => 'Schoolarship Amount is required',
            'schoolarship_amount.number' => 'Schoolarship Amount must be a number',
            'contract_amount.require' => 'Contract Amount is required',
            'contract_amount.number' => 'Contract Amount must be a number',
            'contract_amount.gt' => 'Contract Amount must be greater than 0',
            'book_fee.require' => 'Book Fee is required',
            'book_fee.number' => 'Book Fee must be a number',
            'book_fee.egt' => 'Book Fee must be equal or greater than 0',
            'enrollment_fee.require' => 'Enrollment Fee is required',
            'enrollment_fee.number' => 'Enrollment Fee must be a number',
            'enrollment_fee.egt' => 'Enrollment Fee must be equal or greater than 0',
            'status.require' => 'Status is required',
            'payment_times.require' => 'Payment Times is required',
            'payment_times.number' => 'Payment Times must be a number',
            'payment_times.between' => 'Payment Times must be between 1 and 4',
            'payment_time_1.require' => 'Payment Time 1 is required',
            'payment_amount_1.require' => 'Payment Amount 1 is required',
            'payment_amount_1.number' => 'Payment Amount 1 must be a number',
            'payment_amount_1.gt' => 'Payment Amount 1 must be greater than 0',
            'payment_lessons_1.require' => 'Payment Lessons 1 is required',
            'payment_lessons_1.number' => 'Payment Lessons 1 must be a number',
            'payment_lessons_1.gt' => 'Payment Lessons 1 must be greater than 0',
            'payment_time_2.requireIf' => 'Payment Time 2 is required when payment times > 1',
            'payment_amount_2.requireIf' => 'Payment Amount 2 is required when payment times > 1',
            'payment_amount_2.number' => 'Payment Amount 2 must be a number',
            'payment_amount_2.gt' => 'Payment Amount 2 must be greater than 0',
            'payment_lessons_2.requireIf' => 'Payment Lessons 2 is required when payment times > 1',
            'payment_lessons_2.number' => 'Payment Lessons 2 must be a number',
            'payment_lessons_2.gt' => 'Payment Lessons 2 must be greater than 0',
            'payment_time_3.requireIf' => 'Payment Time 3 is required when payment times > 2',
            'payment_amount_3.requireIf' => 'Payment Amount 3 is required when payment times > 2',
            'payment_amount_3.number' => 'Payment Amount 3 must be a number',
            'payment_amount_3.gt' => 'Payment Amount 3 must be greater than 0',
            'payment_lessons_3.requireIf' => 'Payment Lessons 3 is required when payment times > 2',
            'payment_lessons_3.number' => 'Payment Lessons 3 must be a number',
            'payment_lessons_3.gt' => 'Payment Lessons 3 must be greater than 0',
            'payment_time_4.requireIf' => 'Payment Time 4 is required when payment times > 3',
            'payment_amount_4.requireIf' => 'Payment Amount 4 is required when payment times > 3',
            'payment_amount_4.number' => 'Payment Amount 4 must be a number',
            'payment_amount_4.gt' => 'Payment Amount 4 must be greater than 0',
            'payment_lessons_4.requireIf' => 'Payment Lessons 4 is required when payment times > 3',
            'payment_lessons_4.number' => 'Payment Lessons 4 must be a number',
            'payment_lessons_4.gt' => 'Payment Lessons 4 must be greater than 0',
        ]);

        if (!$validate->check($data)) {
            return ['code'=>400, 'msg'=>$validate->getError(), 'state'=>'INVALID_ARGUMENT', 'data'=>null];
        }

        // 启用事务将数据保存到数据库
        Db::startTrans();
        try {
            // 准备合同数据
            $contract = [
                'customers_id' => $data['customers_id'],
                'created_by' => $auth->id,
                'contract_type' => $data['contract_type'],
                'course_category' => $data['course_category'],
                'subcategory' => $data['subcategory'] ?? null,
                'contract_lessons' => $data['contract_lessons'],
                'contract_length' => $data['contract_length'],
                'start_date' => $data['start_date'],
                'end_date' => $data['end_date'],
                'contract_lessons_paid' => $data['contract_lessons_paid'],
                'contract_lessons_free' => $data['contract_lessons_free'],
                'other_office' => $data['other_office'] ?? null,
                'start_level' => $data['start_level'],
                'end_level' => $data['end_level'],
                'cool_coin' => $data['cool_coin'],
                'consultant' => $data['consultant'],
                'in_charge_teacher' => $data['in_charge_teacher'],
                'teacher_kpi' => $data['teacher_kpi'],
                'currency' => $data['currency'],
                'contract_tuition' => $data['contract_tuition'],
                'schoolarship_amount' => $data['schoolarship_amount'],
                'contract_amount' => $data['contract_amount'],
                'book_fee' => $data['book_fee'],
                'enrollment_fee' => $data['enrollment_fee'],
                'other_fee' => $data['other_fee'] ?? 0,
                'status' => $data['status']
            ];

            // 生成合同编号
            $count = \app\admin\model\CustomersContract::where('customers_id', $data['customers_id'])->count();
            $customers = \app\admin\model\UserCustomers::where('id', $data['customers_id'])->find();
            $contract['contract_no'] = $customers['studio_id'] . '0' . sprintf("%06d", ($count+1));

            // 创建合同
            $res = \app\admin\model\CustomersContract::create($contract);

            // 准备订单数据
            $order = [
                'customers_id' => $data['customers_id'],
                'type' => $data['contract_type'],
                'link_id' => $res['id'],
                'order_no' => getNewOrderNo(),
                'currency' => $data['currency'],
                'amount' => $data['contract_amount'],
                'item' => json_encode([
                    'category' => $data['course_category'],
                    'subcategory' => $data['subcategory'] ?? null,
                    'payment_times' => $data['payment_times'],
                    'book_fee' => $data['book_fee'],
                    'enrollment_fee' => $data['enrollment_fee'],
                    'other_fee' => $data['other_fee'] ?? 0,
                    'payment_time_1' => $data['payment_time_1'],
                    'payment_amount_1' => $data['payment_amount_1'],
                    'payment_lessons_1' => $data['payment_lessons_1'],
                    'payment_time_2' => $data['payment_time_2'],
                    'payment_amount_2' => $data['payment_amount_2'],
                    'payment_lessons_2' => $data['payment_lessons_2'],
                    'payment_time_3' => $data['payment_time_3'],
                    'payment_amount_3' => $data['payment_amount_3'],
                    'payment_lessons_3' => $data['payment_lessons_3'],
                    'payment_time_4' => $data['payment_time_4'],
                    'payment_amount_4' => $data['payment_amount_4'],
                    'payment_lessons_4' => $data['payment_lessons_4']
                ])
            ];

            // 创建订单
            $ret = \app\admin\model\CustomersOrder::create($order);

            if ($res && $ret) {
                Db::commit();
                return [
                    'code' => 200,
                    'msg' => 'Successful',
                    'state' => 'OK',
                    'data' => [
                        'contract_id' => $res['id'],
                        'contract_no' => $contract['contract_no'],
                        'order_id' => $ret['id'],
                        'order_no' => $order['order_no']
                    ]
                ];
            } else {
                Db::rollback();
                return ['code'=>400, 'msg'=>'Failed to create contract and order', 'state'=>'FAILED', 'data'=>null];
            }
        } catch (\Exception $e) {
            Db::rollback();
    //        \think\facade\Log::error('Contract creation failed: ' . $e->getMessage());
            return [
                'code' => 400,
                'msg' => 'Database error occurred',
                'state' => 'DATABASE_ERROR',
                'data' => null
            ];
        }
    }


    // 确认支付
    public static function confirm_payment($data)
    {
        $data['paidtime'] = $data['payment_date'];
        $data['payment_by'] = $data['payment_collected_by'];
        $data['amount'] = $data['payment_amount'];
        unset($data['payment_date']);
        unset($data['payment_collected_by']);
        unset($data['payment_amount']);

        $auth = Auth::instance();
        // 验证规则
        $validate = new Validate([
            'id' => 'require',
            'book_fee' => 'require',
            'enrollment_fee' => 'require',
            'amount' => 'require',
            'method' => 'require',
            'paidtime' => 'require',
            'payment_by' => 'require',
        ],[
            'id.require' => 'ID is not empty',
            'book_fee.require' => 'Book Fee is not empty',
            'enrollment_fee.require' => 'Enrollment Fee is not empty',
            'amount.require' => 'Amount is not empty',
            'method.require' => 'Method is not empty',
            'paidtime.require' => 'Payment Date is not empty',
            'payment_by.require' => 'Payment Collected By is not empty',
        ]);

        if (!$validate->check($data)) {
            return ['code'=>400, 'msg'=>$validate->getError(), 'state'=>'INVALID_ARGUMENT', 'data'=>null];
        }

        $contract = \app\admin\model\CustomersContract::where('id', $data['id'])->find();
        if($contract['status'] == 'CANCELLED'){
            return ['code'=>400, 'msg'=>'Contract is cancelled', 'state'=>'CONTRACT_CANCELLED', 'data'=>null];
        }

        $order = \app\admin\model\CustomersOrder::where(['link_id'=>$data['id'],'paid'=>['<>','YES']])->find();
        if(!$order) return ['code'=>400, 'msg'=>'Order not found', 'state'=>'ORDER_NOT_FOUND', 'data'=>null];

        // 启用事务将数据保存到数据库
        Db::startTrans();
        try {
            $item = json_decode($order['item'], true);
            $item['book_fee'] = $data['book_fee'];
            $item['enrollment_fee'] = $data['enrollment_fee'];
            $item['other_fee'] = $data['other_fee'];
            $payment_money = 0;

            if ($item['payment_times'] <= 0) {
                throw new \Exception('Invalid payment times');
            }

            for($i=0; $i<$item['payment_times']; $i++){
                $k = $i+1;
                $payment_money += bcdiv($contract['contract_amount'], $item['payment_times'], 2);
                if($item['payment_amount_' . $k] == 0){
                    $item['payment_amount_' . $k] = bcdiv($contract['contract_amount'], $item['payment_times'], 2);
                    $item['payment_lessons_' . $k] = bcdiv($contract['contract_lessons'], $item['payment_times'], 2);
                    break;
                }
            }

            $item['transaction'][] = "Paid {$data['amount']} via {$data['method']} on {$data['paidtime']}";
            $data['paid'] = (count($item['transaction'])+1 == $item['payment_times']) ? 'YES' : 'SECTIONS';
            $data['item'] = json_encode($item);

            unset($data['book_fee']);
            unset($data['enrollment_fee']);
            unset($data['other_fee']);

            $result = \app\admin\model\CustomersOrder::where(['link_id'=>$data['id']])->update($data);

            if($result){
                Db::commit();
                $info['nickname'] = \app\admin\model\UserCustomers::where('id', $order['customers_id'])->value('english_name');
                $info['contract_tuition'] = $contract['contract_tuition'];
                $info['actual_amount'] = $order['amount'];
                $info['order_no'] = $order['order_no'];
                $info['schoolarship_amount'] = $contract['schoolarship_amount'];
                $info['due'] = $info['contract_tuition'] - $info['schoolarship_amount'] - $payment_money;

                return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>$info];
            }else{
                Db::rollback();
                return ['code'=>400, 'msg'=>'Failed', 'state'=>'FAILED', 'data'=>null];
            }
        }
        catch (\Exception $e) {
            Db::rollback();
            return ['code' => 400, 'msg' => 'Database error: ' . $e->getMessage(), 'state' => 'DATABASE_ERROR', 'data' => null];
        }
    }


    // 合同列表
    public static function contract_list(array $data): array
    {
        try {
            $auth = Auth::instance();
            // 验证用户是否登录
            if (!$auth->isLogin()) {
                return ['code' => 403, 'msg' => 'Unauthorized', 'state' => 'ERROR'];
            }

            // 验证输入参数
            if (!isset($data['customers_id']) || !is_numeric($data['customers_id'])) {
                return ['code' => 400, 'msg' => 'Invalid customers_id', 'state' => 'ERROR'];
            }

            $customers_id = (int)$data['customers_id'];
            $limit = isset($data['limit']) && $data['limit'] ? $data['limit'] : 10;
            $limit = max(1, min(100, (int)$limit)); // 限制分页范围1-100

            // 查询数据
            $list = \app\admin\model\CustomersContract::where(['customers_id' => $customers_id])
                ->field('id,contract_no')
                ->order('id desc')
                ->paginate($limit);

            return ['code' => 200, 'msg' => 'Successful', 'state' => 'OK', 'data' => $list];
        } catch (\Exception $e) {
            // 记录日志
            \think\facade\Log::error('Contract list error: ' . $e->getMessage());
            return ['code' => 500, 'msg' => 'Server Error', 'state' => 'ERROR'];
        }
    }


    // 合同详情
    public static function contract_view($data)
    {
        if (!isset($data['id'])) {
            return ['code'=>400, 'msg'=>'ID is required', 'state'=>'ID_REQUIRED', 'data'=>null];
        }

        $id = intval($data['id']);
        $contract = \app\admin\model\CustomersContract::where(['id'=>$id])->find();
        if (!$contract) return ['code'=>400, 'msg'=>'Contract not found', 'state'=>'CONTRACT_NOT_FOUND', 'data'=>null];

        $customers = \app\admin\model\UserCustomers::where(['id'=>$contract['customers_id']])->find();
        if (!$customers) return ['code'=>400, 'msg'=>'Customers not found', 'state'=>'CUSTOMERS_NOT_FOUND', 'data'=>null];

        $order = \app\admin\model\CustomersOrder::where(['link_id'=>$id])->find();
    //    return $order;
        if (!$order) return ['code'=>400, 'msg'=>'Order not found', 'state'=>'ORDER_NOT_FOUND', 'data'=>null];

        $item = json_decode($order['item'], true);
        if (!is_array($item)) {
            return ['code'=>400, 'msg'=>'Invalid order item', 'state'=>'INVALID_ORDER_ITEM', 'data'=>null];
        }

        $contract_type = \app\admin\model\Course::where(['course_id'=>$contract['contract_type']])->field('course_id,course_name')->find();
        if(!$contract_type) return ['code'=>400, 'msg'=>'Contract type not found', 'state'=>'CONTRACT_TYPE_NOT_FOUND', 'data'=>null];

        $contract_category = \app\admin\model\StudentCourseCategory::where(['category_id'=>$contract['course_category']])->field('category_id,category_name,abbreviation')->find();
        if(!$contract_category) return ['code'=>400, 'msg'=>'Contract Category not found', 'state'=>'CONTRACT_CATEGORY_NOT_FOUND', 'data'=>null];

        // Calculate age properly
        $birthDate = new \DateTime($customers['birthday']);
        $today = new \DateTime();
        $age = $today->diff($birthDate)->y;

        $result = [
            'contract#' => $contract['contract_no'],
            'contract_type' => $contract_type['course_name'],
            'contract_category' => $contract_category['abbreviation'],
            'full_name' => $customers['surname'] . ' ' . $customers['given_name'],
            'english_name' => $customers['english_name'],
            'status' => $contract['status'],
            'age' => $age,
            'gender' => $customers['gender'],
            'birthday' => $customers['birthday'],
            'mobile' => $customers['mobile'],
            'channel' => \app\admin\model\UserChannel::where(['id'=>$customers['channel']])->value('name'),
            'crated_by' => $customers['collected_id'] ? \app\admin\model\User::where(['id'=>$customers['collected_id']])->value('nickname') : '',
            'create_date' => $customers['createtime'] ? date('Y-m-d H:i:s', $customers['createtime']) : '',
            'has_approved' => $contract['approved_by'] ? 'YES' : 'NO',
            'approved_by' => $contract['approved_by'] ? \app\admin\model\User::where(['id'=>$contract['approved_by']])->value('nickname') : '',
            'approved_date' => $contract['approvedtime'] ? date('Y-m-d H:i:s', $contract['approvedtime']) : '',
            'contract_tuition' => $contract['contract_tuition'],
            'contract_amount' => $contract['contract_amount'],
            'schoolarship_amount' => $contract['schoolarship_amount'],
            'contract_lessons_due' => $contract['contract_lessons'] - $contract['contract_lessons_free'],
            'payment_times' => $item['payment_times'],
            'book_fee' => $item['book_fee'],
            'enrollment_fee' => $item['enrollment_fee'],
            'other_fee' => $item['other_fee']
        ];

        $totalAmount = (float)$order['amount'];
        for($i = 0; $i < $item['payment_times']; $i++) {
            $k = $i + 1;
            $paymentKey = 'payment_amount_' . $k;
            $timeKey = 'payment_time_' . $k;

            if (isset($item[$paymentKey]) && isset($item[$timeKey])) {
                $paymentAmount = is_numeric($item[$paymentKey]) ? (float)$item[$paymentKey] : 0;
                $result[$timeKey] = is_numeric($item[$timeKey]) ? (float)$item[$timeKey] : 0;
                $result[$paymentKey] = $paymentAmount;

                if ($paymentAmount > 0) {
                    $result['payment_status_' . $k] = 'Paid';
                    $totalAmount -= $paymentAmount;
                } else {
                    $result['payment_status_' . $k] = 'Unpaid';
                }
            }
        }

        return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>$result];
    }


    // 批准合同
    public static function contract_approval($data)
{
    $data['approvedtime'] = $data['approved_date'];
    unset($data['approved_date']);

    // 验证规则
    $validate = new Validate([
        'id' => 'require',
        'approvedtime' => 'require|date',
        'approved_by' => 'require',
    ],[
        'id.require' => 'ID is not empty',
        'approvedtime.require' => 'Approved Date is not empty',
        'approved_by.require' => 'Approved By is not empty',
    ]);

    if (!$validate->check($data)) {
        return ['code'=>400, 'msg'=>$validate->getError(), 'state'=>'INVALID_ARGUMENT', 'data'=>null];
    }

    try {
        $data['approvedtime'] = strtotime($data['approvedtime']);
        $result = \app\admin\model\CustomersContract::update($data);

        if ($result) {
            return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>null];
        } else {
            return ['code'=>400, 'msg'=>'Failed', 'state'=>'FAILED', 'data'=>null];
        }
    } catch (\Exception $e) {
        return ['code'=>400, 'msg'=>$e->getMessage(), 'state'=>'ERROR', 'data'=>null];
    }
}


    // 签署人
    public static function contract_signed($data)
    {
        // 输入验证
        if (empty($data['id'])) {
            return ['code'=>400, 'msg'=>'Contract ID is required', 'state'=>'INVALID_INPUT', 'data'=>null];
        }

        try {
            Db::startTrans();
            $contract = \app\admin\model\CustomersContract::where(['id'=>$data['id']])->find();
            if (!$contract) {
                Db::rollback();
                return ['code'=>400, 'msg'=>'Contract not found', 'state'=>'CONTRACT_NOT_FOUND', 'data'=>null];
            }

            $result = \app\admin\model\CustomersContract::update($data);
            if ($result) {
                Db::commit();
                return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>$result];
            } else {
                Db::rollback();
                return ['code'=>400, 'msg'=>'Update failed', 'state'=>'FAILED', 'data'=>null];
            }
        } catch (\Exception $e) {
            Db::rollback();
            // 记录日志
            \think\facade\Log::error('Contract sign error: '.$e->getMessage());
            return ['code'=>500, 'msg'=>'System error', 'state'=>'ERROR', 'data'=>null];
        }
    }


    // 取消合同
    public static function contract_cancel($data)
    {
        if (!is_array($data)) {
            return ['code'=>400, 'msg'=>'Input data must be an array', 'state'=>'INVALID_ARGUMENT', 'data'=>null];
        }

        $auth = Auth::instance();
        // 验证规则
        $validate = new Validate([
            'id' => 'require',
            'balance' => 'require',
            'lessons_remaining' => 'require',
            'contract_lesson_free' => 'require',
            'total_lesson_remaining' => 'require',
            'cancellation_value' => 'require',
            'cancellation_lesson_number' => 'require',
            'cancellation_reason' => 'require',
            'cancellation_date' => 'require',
        ],[
            'id.require' => 'ID is not empty',
            'balance.require' => 'Balance is not empty',
            'lessons_remaining.require' => 'Lessons Remaining is not empty',
            'contract_lesson_free.require' => 'Contract Lesson Free is not empty',
            'total_lesson_remaining.require' => 'Total Lesson Remaining is not empty',
            'cancellation_value.require' => 'Cancelation Value is not empty',
            'cancellation_lesson_number.require' => 'Cancellation Lesson Number is not empty',
            'cancellation_reason.require' => 'Cancelation Reason is not empty',
            'cancellation_date.require' => 'Cancelation Date is not empty',
        ]);

        if (!$validate->check($data)) {
            return ['code'=>400, 'msg'=>$validate->getError(), 'state'=>'INVALID_ARGUMENT', 'data'=>null];
        }

        try {
            Db::startTrans();
            $id = $data['id'];
            unset($data['id']);
            $cancel_item = json_encode($data);
            if ($cancel_item === false) {
                Db::rollback();
                return ['code'=>400, 'msg'=>'Failed to encode cancellation data', 'state'=>'ERROR', 'data'=>null];
            }

            $result = \app\admin\model\CustomersContract::where(['id'=>$id])->update([
                'cancel_item' => $cancel_item,
                'status' => 'cancel'
            ]);

            if ($result) {
                Db::commit();
                return ['code'=>200, 'msg'=>'Successful', 'state'=>'OK', 'data'=>null];
            } else {
                Db::rollback();
                return ['code'=>400, 'msg'=>'Failed', 'state'=>'FAILED', 'data'=>null];
            }
        } catch (\Exception $e) {
            Db::rollback();
            return ['code'=>400, 'msg'=>$e->getMessage(), 'state'=>'ERROR', 'data'=>null];
        }
    }


    // 收据
    public static function contract_receipt(array $data): array
    {
        $id = $data['id'] ?? null;
        $payment_times = $data['payment_times'] ?? null;

        if (!$id || !$payment_times || !is_numeric($payment_times)) {
            return ['code' => 400, 'msg' => 'Invalid input', 'state' => 'INVALID_INPUT', 'data' => null];
        }

        $contract = \app\admin\model\CustomersContract::where(['id' => $id])->find();
        if (!$contract) return ['code' => 400, 'msg' => 'Contract not found', 'state' => 'CONTRACT_NOT_FOUND', 'data' => null];

        $order = \app\admin\model\CustomersOrder::where(['link_id' => $id])->find();
        if (!$order) return ['code' => 400, 'msg' => 'Order not found', 'state' => 'ORDER_NOT_FOUND', 'data' => null];

        $item = json_decode($order['item'], true) ?? [];
        if (json_last_error() !== JSON_ERROR_NONE) {
            return ['code' => 400, 'msg' => 'Invalid item data', 'state' => 'INVALID_ITEM_DATA', 'data' => null];
        }

        $transaction = $item['transaction'] ?? [];
        $key = (int)$payment_times - 1;

        if (!isset($transaction[$key])) {
            return ['code' => 400, 'msg' => 'Invalid payment times', 'state' => 'INVALID_PAYMENT_TIMES', 'data' => null];
        }

        $contractAmount = $contract['contract_amount'];
        $receipt = [
            'receipt_#' => $order['order_no'],
            'contract_type' => $contract['contract_type'],
            'category' => $contract['course_category'],
            'subcategory' => $contract['subcategory'],
            'contract_tuition' => $contract['contract_tuition'],
            'contract_amount' => $contractAmount,
            'scholarship_amount' => $contract['schoolarship_amount'],
            'payment_date' => substr($transaction[$key], -10),
            'payment' => substr($transaction[$key], 5, 3),
            'payment_collected_by' => \app\admin\model\User::where(['id' => $order['payment_by']])->value('nickname'),
            'total' => $contractAmount
        ];

        $due = $contractAmount;
        for ($i = 1; $i <= $payment_times; $i++) {
            $due -= (float)($item['payment_amount_' . $i] ?? 0);
        }
        $receipt['due'] = $due;

        return ['code' => 200, 'msg' => 'Successful', 'state' => 'OK', 'data' => $receipt];
    }

}
